insert overwrite table jms_dm.dm_netmng_network_retention_agent_summary_dt partition (dt)
--网点滞留件汇总新
select t1.is_contain_difficult --是否包含问题件 1 是 2 否
     , t1.end_agent_code       --末端网点所属代理区编码
     , t1.end_agent_name       --末端网点所属代理区名称
     , t2.virt_code            --虚拟代理区code
     , t2.virt_name            --虚拟代理区name
     , t1.need_sign_count_1d   --1天应签收
     , t1.retention_count_1d   --1天内滞留
     , t1.need_sign_count_2d   --2天应签收
     , t1.retention_count_2d   --2天内滞留
     , t1.need_sign_count_3d   --3天应签收
     , t1.retention_count_3d   --3天内滞留
     , t1.retention_count_4d   --4天内滞留
     , t1.need_sign_count_4d   --4天应签收
     , t1.need_sign_count_5d   --5天应签收
     , t1.retention_count_5d   --5天内滞留
     , t1.retention_count_6d   --6天内滞留
     , t1.need_sign_count_6d   --6天应签收
     , t1.need_sign_count_7d   --7天应签收
     , t1.retention_count_7d   --7天内滞留
     , t1.need_sign_over_7d    --超7天应签收
     , t1.retention_over_7d    --超7天内滞留
     , t1.date_time            --日期
     , t1.dt
from (
         select is_contain_difficult            as is_contain_difficult --是否包含问题件 1 是 2 否
              , end_agent_code                  as end_agent_code       --末端网点所属代理区编码
              , end_agent_name                  as end_agent_name       --末端网点所属代理区名称
              , sum(nvl(need_sign_count_1d, 0)) as need_sign_count_1d   --1天应签收
              , sum(nvl(retention_count_1d, 0)) as retention_count_1d   --1天内滞留
              , sum(nvl(need_sign_count_2d, 0)) as need_sign_count_2d   --2天应签收
              , sum(nvl(retention_count_2d, 0)) as retention_count_2d   --2天内滞留
              , sum(nvl(need_sign_count_3d, 0)) as need_sign_count_3d   --3天应签收
              , sum(nvl(retention_count_3d, 0)) as retention_count_3d   --3天内滞留
              , sum(nvl(retention_count_4d, 0)) as retention_count_4d   --4天内滞留
              , sum(nvl(need_sign_count_4d, 0)) as need_sign_count_4d   --4天应签收
              , sum(nvl(need_sign_count_5d, 0)) as need_sign_count_5d   --5天应签收
              , sum(nvl(retention_count_5d, 0)) as retention_count_5d   --5天内滞留
              , sum(nvl(retention_count_6d, 0)) as retention_count_6d   --6天内滞留
              , sum(nvl(need_sign_count_6d, 0)) as need_sign_count_6d   --6天应签收
              , sum(nvl(need_sign_count_7d, 0)) as need_sign_count_7d   --7天应签收
              , sum(nvl(retention_count_7d, 0)) as retention_count_7d   --7天内滞留
              , sum(nvl(need_sign_over_7d, 0))  as need_sign_over_7d    --超7天应签收
              , sum(nvl(retention_over_7d, 0))  as retention_over_7d    --超7天内滞留
              , dt                              as date_time            --日期
              , dt                              as dt
         from jms_dm.dm_network_retention_summary_dt
         where dt <= '{{ execution_date | cst_ds }}'
           and dt >= date_add('{{ execution_date | cst_ds }}', -4)
         group by is_contain_difficult --是否包含问题件 1 是 2 否
                , end_agent_code       --末端网点所属代理区编码
                , end_agent_name       --末端网点所属代理区名称
                , dt
     ) t1
         left join jms_dim.dim_network_whole_massage t2
                   on t1.end_agent_code = t2.code
    distribute by dt
